package com.ant.collect.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class test {
    public static void main(String[] args) {
        String inputFilePath = "f:/hb/123.xlsx";
        // 2. 写入新Excel（替换为你的输出文件路径）
        String outputFilePath = "f:/hb/11122.xlsx";
        try {
            // 读取输入Excel文件
            Workbook inputWorkbook = getWorkbook(inputFilePath);
            Sheet inputSheet = inputWorkbook.getSheetAt(0);

            // 处理数据
            List<ProcessedData> processedDataList = processExcelData(inputSheet);

            // 创建新的Excel工作簿
            Workbook outputWorkbook = new XSSFWorkbook();
            Sheet outputSheet = outputWorkbook.createSheet("Processed Data");

            // 写入表头
            writeHeader(outputSheet);

            // 写入处理后的数据
            writeData(outputSheet, processedDataList);

            // 保存新的Excel文件
            try (FileOutputStream outputStream = new FileOutputStream(outputFilePath)) {
                outputWorkbook.write(outputStream);
            }

            System.out.println("数据处理并保存成功！");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static Workbook getWorkbook(String filePath) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(new File(filePath));
        if (filePath.endsWith(".xlsx")) {
            return new XSSFWorkbook(fileInputStream);
        } else {
            return new HSSFWorkbook(fileInputStream);
        }
    }

    private static List<ProcessedData> processExcelData(Sheet inputSheet) {
        List<ProcessedData> processedDataList = new ArrayList<>();
        List<String> parentCodes = new ArrayList<>();

        for (Row row : inputSheet) {
            String code = "";
            String name = "";
            int level = 1;

            for (Cell cell : row) {
                if (cell.getColumnIndex() < 4 &&!getCellValue(cell).isEmpty()) {
                    code = getCellValue(cell);
                    // 尝试根据数字位数确定层级，比如 1 是一级，11 是二级
                    level = code.length();
                }
                if (cell.getColumnIndex() == 4) {
                    name = getCellValue(cell);
                }
            }

            if (code.isEmpty() && name.isEmpty()) {
                continue;
            }

            String parentId;
            if (level == 1) {
                parentId = "0";
                parentCodes.clear();
            } else {
                // 根据当前层级获取上一级的 parentCode
                if (parentCodes.size() >= level - 1) {
                    parentId = parentCodes.get(level - 2);
                } else {
                    parentId = "0";
                }
            }

            processedDataList.add(new ProcessedData(code, name, parentId, level));

            if (parentCodes.size() < level) {
                parentCodes.add(level - 1, code);
            } else {
                parentCodes.set(level - 1, code);
            }
        }

        return processedDataList;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    private static void writeHeader(Sheet sheet) {
        Row headerRow = sheet.createRow(0);
        String[] headers = {"code", "name", "parent_id", "level"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
        }
    }

    private static void writeData(Sheet sheet, List<ProcessedData> processedDataList) {
        int rowIndex = 1;
        for (ProcessedData data : processedDataList) {
            Row row = sheet.createRow(rowIndex++);
            Cell codeCell = row.createCell(0);
            Cell nameCell = row.createCell(1);
            Cell parentIdCell = row.createCell(2);
            Cell levelCell = row.createCell(3);

            codeCell.setCellValue(data.getCode());
            nameCell.setCellValue(data.getName());
            parentIdCell.setCellValue(data.getParentId());
            levelCell.setCellValue(data.getLevel());
        }
    }

//        try {
//            // 读取输入Excel文件
//            Workbook inputWorkbook = getWorkbook(inputFilePath);
//            Sheet inputSheet = inputWorkbook.getSheetAt(0);
//
//            // 处理数据
//            List<ProcessedData> processedDataList = processExcelData(inputSheet);
//
//            // 创建新的Excel工作簿
//            Workbook outputWorkbook = new XSSFWorkbook();
//            Sheet outputSheet = outputWorkbook.createSheet("Processed Data");
//
//            // 写入表头
//            writeHeader(outputSheet);
//
//            // 写入处理后的数据
//            writeData(outputSheet, processedDataList);
//
//            // 保存新的Excel文件
//            try (FileOutputStream outputStream = new FileOutputStream(outputFilePath)) {
//                outputWorkbook.write(outputStream);
//            }
//
//            System.out.println("数据处理并保存成功！");
//
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//    }
//    private static Workbook getWorkbook(String filePath) throws IOException {
//        FileInputStream fileInputStream = new FileInputStream(new File(filePath));
//        if (filePath.endsWith(".xlsx")) {
//            return new XSSFWorkbook(fileInputStream);
//        } else {
//            return new HSSFWorkbook(fileInputStream);
//        }
//    }
//
//    private static List<ProcessedData> processExcelData(Sheet inputSheet) {
//        List<ProcessedData> processedDataList = new ArrayList<>();
//        List<String> parentCodes = new ArrayList<>();
//
//        for (Row row : inputSheet) {
//            boolean hasCode = false;
//            String code = "";
//            String name = "";
//            int level = 1;
//
//            for (Cell cell : row) {
//                if (!hasCode && cell.getColumnIndex() < 4 &&!getCellValue(cell).isEmpty()) {
//                    code = getCellValue(cell);
//                    hasCode = true;
//                }
//                if (cell.getColumnIndex() == 4) {
//                    name = getCellValue(cell);
//                }
//            }
//
//            if (code.isEmpty() && name.isEmpty()) {
//                continue;
//            }
//
//            if (hasCode) {
//                level = getLevelFromCode(code);
//            }
//
//            String parentId;
//            if (level == 1) {
//                parentId = "0";
//                parentCodes.clear();
//            } else {
//                parentId = parentCodes.get(level - 2);
//            }
//
//            processedDataList.add(new ProcessedData(code, name, parentId, level));
//
//            if (parentCodes.size() < level) {
//                parentCodes.add(level - 1, code);
//            } else {
//                parentCodes.set(level - 1, code);
//            }
//        }
//
//        return processedDataList;
//    }
//
//    private static int getLevelFromCode(String code) {
//        return code.replaceAll("[^.]", "").length() + 1;
//    }
//
//    private static String getCellValue(Cell cell) {
//        if (cell == null) {
//            return "";
//        }
//
//        switch (cell.getCellType()) {
//            case STRING:
//                return cell.getStringCellValue();
//            case NUMERIC:
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    return cell.getDateCellValue().toString();
//                } else {
//                    return String.valueOf((int) cell.getNumericCellValue());
//                }
//            case BOOLEAN:
//                return String.valueOf(cell.getBooleanCellValue());
//            case FORMULA:
//                return cell.getCellFormula();
//            default:
//                return "";
//        }
//    }
//
//    private static void writeHeader(Sheet sheet) {
//        Row headerRow = sheet.createRow(0);
//        String[] headers = {"code", "name", "parent_id", "level"};
//        for (int i = 0; i < headers.length; i++) {
//            Cell cell = headerRow.createCell(i);
//            cell.setCellValue(headers[i]);
//        }
//    }
//
//    private static void writeData(Sheet sheet, List<ProcessedData> processedDataList) {
//        int rowIndex = 1;
//        for (ProcessedData data : processedDataList) {
//            Row row = sheet.createRow(rowIndex++);
//            Cell codeCell = row.createCell(0);
//            Cell nameCell = row.createCell(1);
//            Cell parentIdCell = row.createCell(2);
//            Cell levelCell = row.createCell(3);
//
//            codeCell.setCellValue(data.getCode());
//            nameCell.setCellValue(data.getName());
//            parentIdCell.setCellValue(data.getParentId());
//            levelCell.setCellValue(data.getLevel());
//        }
//    }

    static class ProcessedData {
        private String code;
        private String name;
        private String parentId;
        private int level;

        public ProcessedData(String code, String name, String parentId, int level) {
            this.code = code;
            this.name = name;
            this.parentId = parentId;
            this.level = level;
        }

        public String getCode() {
            return code;
        }

        public String getName() {
            return name;
        }

        public String getParentId() {
            return parentId;
        }

        public int getLevel() {
            return level;
        }
    }
}
